********************************************************************************
** 	TITLE:		DE17_results                                                  ** 	
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       btw17_kerg                                                    **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* For incumbent reelection

do DE13_17_results

* 1. Download the "btw17_kerg.csv" file from this link: https://www.bundeswahlleiter.de/en/dam/jcr/72f186bb-aa56-47d3-b24c-6a46f5de22d0/btw17_kerg.csv
* 2. Save the "btw17_kerg.csv" file to the Excel format (i.e., "btw17_kerg.xlsx"")
* 3. Verify that the name of the Excel sheet is "btw17_kerg"

* Import the dataset

import excel "btw17_kerg.xlsx", sheet("btw17_kerg") cellrange(A9:GI340) clear

* Rename variables

rename A districtnumber17 
rename B districtname17 
rename C state17 
rename D v17_1_Wahlberechtigte_1_End
rename E v13_1_Wahlberechtigte_1_Vor
rename F v17_2_Wahlberechtigte_2_End
rename G v13_2_Wahlberechtigte_2_Vor
rename H v17_1_Wähler_1_End
rename I v13_1_Wähler_1_Vor
rename J v17_2_Wähler_2_End
rename K v13_2_Wähler_2_Vor
rename L v17_1_Ungültige_1_End
rename M v13_1_Ungültige_1_Vor
rename N v17_2_Ungültige_2_End
rename O v13_2_Ungültige_2_Vor
rename P v17_1_Gültige_1_End
rename Q v13_1_Gültige_1_Vor
rename R v17_2_Gültige_2_End
rename S v13_2_Gültige_2_Vor
rename T v17_1_CDU_1_End
rename U v13_1_CDU_1_Vor
rename V v17_2_CDU_2_End
rename W v13_2_CDU_2_Vor
rename X v17_1_SPD_1_End
rename Y v13_1_SPD_1_Vor
rename Z v17_2_SPD_2_End
rename AA v13_2_SPD_2_Vor
rename AB v17_1_DIELINKE_1_End
rename AC v13_1_DIELINKE_1_Vor
rename AD v17_2_DIELINKE_2_End
rename AE v13_2_DIELINKE_2_Vor
rename AF v17_1_GRÜNE_1_End
rename AG v13_1_GRÜNE_1_Vor
rename AH v17_2_GRÜNE_2_End
rename AI v13_2_GRÜNE_2_Vor
rename AJ v17_1_CSU_1_End
rename AK v13_1_CSU_1_Vor
rename AL v17_2_CSU_2_End
rename AM v13_2_CSU_2_Vor
rename AN v17_1_FDP_1_End
rename AO v13_1_FDP_1_Vor
rename AP v17_2_FDP_2_End
rename AQ v13_2_FDP_2_Vor
rename AR v17_1_AfD_1_End
rename AS v13_1_AfD_1_Vor
rename AT v17_2_AfD_2_End
rename AU v13_2_AfD_2_Vor
rename AV v17_1_PIRATEN_1_End
rename AW v13_1_PIRATEN_1_Vor
rename AX v17_2_PIRATEN_2_End
rename AY v13_2_PIRATEN_2_Vor
rename AZ v17_1_NPD_1_End
rename BA v13_1_NPD_1_Vor
rename BB v17_2_NPD_2_End
rename BC v13_2_NPD_2_Vor
rename BD v17_1_FREIEWÄHLER_1_End
rename BE v13_1_FREIEWÄHLER_1_Vor
rename BF v17_2_FREIEWÄHLER_2_End
rename BG v13_2_FREIEWÄHLER_2_Vor
rename BH v17_1_Tierschutzpartei_1_End
rename BI v13_1_Tierschutzpartei_1_Vor
rename BJ v17_2_Tierschutzpartei_2_End
rename BK v13_2_Tierschutzpartei_2_Vor
rename BL v17_1_ÖDP_1_End
rename BM v13_1_ÖDP_1_Vor
rename BN v17_2_ÖDP_2_End
rename BO v13_2_ÖDP_2_Vor
rename BP v17_1_DiePARTEI_1_End
rename BQ v13_1_DiePARTEI_1_Vor
rename BR v17_2_DiePARTEI_2_End
rename BS v13_2_DiePARTEI_2_Vor
rename BT v17_1_BP_1_End
rename BU v13_1_BP_1_Vor
rename BV v17_2_BP_2_End
rename BW v13_2_BP_2_Vor
rename BX v17_1_Abjetzt_1_End
rename BY v13_1_Abjetzt_1_Vor
rename BZ v17_2_Abjetzt_2_End
rename CA v13_2_Abjetzt_2_Vor
rename CB v17_1_PARTEIDERVERNUNFT_1_End
rename CC v13_1_PARTEIDERVERNUNFT_1_Vor
rename CD v17_2_PARTEIDERVERNUNFT_2_End
rename CE v13_2_PARTEIDERVERNUNFT_2_Vor
rename CF v17_1_MLPD_1_End
rename CG v13_1_MLPD_1_Vor
rename CH v17_2_MLPD_2_End
rename CI v13_2_MLPD_2_Vor
rename CJ v17_1_BüSo_1_End
rename CK v13_1_BüSo_1_Vor
rename CL v17_2_BüSo_2_End
rename CM v13_2_BüSo_2_Vor
rename CN v17_1_SGP_1_End
rename CO v13_1_SGP_1_Vor
rename CP v17_2_SGP_2_End
rename CQ v13_2_SGP_2_Vor
rename CR v17_1_DIERECHTE_1_End
rename CS v13_1_DIERECHTE_1_Vor
rename CT v17_2_DIERECHTE_2_End
rename CU v13_2_DIERECHTE_2_Vor
rename CV v17_1_ADD_1_End
rename CW v13_1_ADD_1_Vor
rename CX v17_2_ADD_2_End
rename CY v13_2_ADD_2_Vor
rename CZ v17_1_Tierschutzallianz_1_End
rename DA v13_1_Tierschutzallianz_1_Vor
rename DB v17_2_Tierschutzallianz_2_End
rename DC v13_2_Tierschutzallianz_2_Vor
rename DD v17_1_B_1_End
rename DE v13_1_B_1_Vor
rename DF v17_2_B_2_End
rename DG v13_2_B_2_Vor
rename DH v17_1_BGE_1_End
rename DI v13_1_BGE_1_Vor
rename DJ v17_2_BGE_2_End
rename DK v13_2_BGE_2_Vor
rename DL v17_1_DiB_1_End
rename DM v13_1_DiB_1_Vor
rename DN v17_2_DiB_2_End
rename DO v13_2_DiB_2_Vor
rename DP v17_1_DKP_1_End
rename DQ v13_1_DKP_1_Vor
rename DR v17_2_DKP_2_End
rename DS v13_2_DKP_2_Vor
rename DT v17_1_DM_1_End
rename DU v13_1_DM_1_Vor
rename DV v17_2_DM_2_End
rename DW v13_2_DM_2_Vor
rename DX v17_1_DieGrauen_1_End
rename DY v13_1_DieGrauen_1_Vor
rename DZ v17_2_DieGrauen_2_End
rename EA v13_2_DieGrauen_2_Vor
rename EB v17_1_DieUrbane_1_End
rename EC v13_1_DieUrbane_1_Vor
rename ED v17_2_DieUrbane_2_End
rename EE v13_2_DieUrbane_2_Vor
rename EF v17_1_Gartenpartei_1_End
rename EG v13_1_Gartenpartei_1_Vor
rename EH v17_2_Gartenpartei_2_End
rename EI v13_2_Gartenpartei_2_Vor
rename EJ v17_1_MW_1_End
rename EK v13_1_MW_1_Vor
rename EL v17_2_MW_2_End
rename EM v13_2_MW_2_Vor
rename EN v17_1_DieHumanisten_1_End
rename EO v13_1_DieHumanisten_1_Vor
rename EP v17_2_DieHumanisten_2_End
rename EQ v13_2_DieHumanisten_2_Vor
rename ER v17_1_Gesund_1_End
rename ES v13_1_Gesund_1_Vor
rename ET v17_2_Gesund_2_End
rename EU v13_2_Gesund_2_Vor
rename EV v17_1_VPartei3_1_End
rename EW v13_1_VPartei3_1_Vor
rename EX v17_2_VPartei3_2_End
rename EY v13_2_VPartei3_2_Vor
rename EZ v17_1_BündnisC_1_End
rename FA v13_1_BündnisC_1_Vor
rename FB v17_2_BündnisC_2_End
rename FC v13_2_BündnisC_2_Vor
rename FD v17_1_DIEEINHEIT_1_End
rename FE v13_1_DIEEINHEIT_1_Vor
rename FF v17_2_DIEEINHEIT_2_End
rename FG v13_2_DIEEINHEIT_2_Vor
rename FH v17_1_DIEVIOLETTEN_1_End
rename FI v13_1_DIEVIOLETTEN_1_Vor
rename FJ v17_2_DIEVIOLETTEN_2_End
rename FK v13_2_DIEVIOLETTEN_2_Vor
rename FL v17_1_FAMILIE_1_End
rename FM v13_1_FAMILIE_1_Vor
rename FN v17_2_FAMILIE_2_End
rename FO v13_2_FAMILIE_2_Vor
rename FP v17_1_DIEFRAUEN_1_End
rename FQ v13_1_DIEFRAUEN_1_Vor
rename FR v17_2_DIEFRAUEN_2_End
rename FS v13_2_DIEFRAUEN_2_Vor
rename FT v17_1_Mieterpartei_1_End
rename FU v13_1_Mieterpartei_1_Vor
rename FV v17_2_Mieterpartei_2_End
rename FW v13_2_Mieterpartei_2_Vor
rename FX v17_1_SL_1_End
rename FY v13_1_SL_1_Vor
rename FZ v17_2_SL_2_End
rename GA v13_2_SL_2_Vor
rename GB v17_1_UNABHÄNGIGE_1_End
rename GC v13_1_UNABHÄNGIGE_1_Vor
rename GD v17_2_UNABHÄNGIGE_2_End
rename GE v13_2_UNABHÄNGIGE_2_Vor
rename GF v17_1_Übrige_1_End
rename GG v13_1_Übrige_1_Vor
rename GH v17_2_Übrige_2_End
rename GI v13_2_Übrige_2_Vor

* Reshape the dataset

drop if districtnumber17 == .
drop if districtname17 == "Bundesgebiet"
drop districtname17
drop if state17 == 99
drop state17

reshape long v17_1_ v13_1_ v17_2_ v13_2_, i(districtnumber17) j(var) string

* Drop second vote and 2013 results

drop if strpos(var, "_2_") > 0
drop if strpos(var, "_Vor") > 0
drop v13_1_ v13_2_ v17_2_

* Generate dichotmous variable for each party 

gen cdu17 = strpos(var, "CDU") > 0
gen spd17 = strpos(var, "SPD") > 0
gen dielinke17 = strpos(var, "DIELINKE") > 0
gen grune17 = strpos(var, "GRÜNE") > 0
gen csu17 = strpos(var, "CSU") > 0
gen fdp17 = strpos(var, "FDP") > 0
gen afd17 = strpos(var, "AfD") > 0
gen piraten17 = strpos(var, "PIRATEN") > 0
gen ndp17 = strpos(var, "NPD") > 0
gen freiewahler17 = strpos(var, "FREIEWÄHLER") > 0
gen tier17 = strpos(var, "Tierschutzpartei") > 0
gen odp17 = strpos(var, "ÖDP") > 0
gen diepartei17 = strpos(var, "DiePARTEI") > 0
gen bp17 = strpos(var, "BP") > 0
gen abjetzt17 = strpos(var, "Abjetzt") > 0
gen vernunft17 = strpos(var, "PARTEIDERVERNUNFT") > 0
gen mlpd17 = strpos(var, "MLPD") > 0
gen buso17 = strpos(var, "BüSo") > 0
gen sgp17 = strpos(var, "SGP") > 0
gen dierechte17 = strpos(var, "DIERECHTE") > 0
gen add17 = strpos(var, "ADD") > 0
gen tierall17 = strpos(var, "Tierschutzallianz") > 0
gen b17 = strpos(var, "B") > 0
gen bge17 = strpos(var, "BGE") > 0
gen dib17 = strpos(var, "DiB") > 0
gen dkp17 = strpos(var, "DKP") > 0
gen dm17 = strpos(var, "DM") > 0
gen diegrauen17 = strpos(var, "DieGrauen") > 0
gen dieurbane17 = strpos(var, "DieUrbane") > 0
gen garten17 = strpos(var, "Gartenpartei") > 0
gen mw17 = strpos(var, "MW") > 0
gen human17 = strpos(var, "DieHumanisten") > 0
gen gesund17 = strpos(var, "Gesund") > 0
gen vpartei17 = strpos(var, "VPartei3") > 0
gen bundnisc17 = strpos(var, "BündnisC") > 0
gen dieeinheit17 = strpos(var, "DIEEINHEIT") > 0
gen violetten17 = strpos(var, "DIEVIOLETTEN") > 0
gen familie17 = strpos(var, "FAMILIE") > 0
gen diefrauen17 = strpos(var, "DIEFRAUEN") > 0
gen mieter17 = strpos(var, "Mieterpartei") > 0
gen sl17 = strpos(var, "SL") > 0
gen unab17 = strpos(var, "UNABHÄNGIGE") > 0
gen other17 = strpos(var, "Übrige") > 0

* Generate district names

gen districtname17 = ""

replace districtname17 = "Flensburg – Schleswig" if districtnumber17 == 1
replace districtname17 = "Nordfriesland – Dithmarschen Nord" if districtnumber17 == 2
replace districtname17 = "Steinburg – Dithmarschen Süd" if districtnumber17 == 3
replace districtname17 = "Rendsburg-Eckernförde" if districtnumber17 == 4
replace districtname17 = "Kiel" if districtnumber17 == 5
replace districtname17 = "Plön – Neumünster" if districtnumber17 == 6
replace districtname17 = "Pinneberg" if districtnumber17 == 7
replace districtname17 = "Segeberg – Stormarn-Mitte" if districtnumber17 == 8
replace districtname17 = "Ostholstein – Stormarn-Nord" if districtnumber17 == 9
replace districtname17 = "Herzogtum Lauenburg – Stormarn-Süd" if districtnumber17 == 10
replace districtname17 = "Lübeck" if districtnumber17 == 11
replace districtname17 = "Schwerin – Ludwigslust-Parchim I – Nordwestmecklenburg I" if districtnumber17 == 12
replace districtname17 = "Ludwigslust-Parchim II – Nordwestmecklenburg II – Landkreis Rostock I" if districtnumber17 == 13
replace districtname17 = "Rostock – Landkreis Rostock II" if districtnumber17 == 14
replace districtname17 = "Vorpommern-Rügen – Vorpommern-Greifswald I" if districtnumber17 == 15
replace districtname17 = "Mecklenburgische Seenplatte I – Vorpommern-Greifswald II" if districtnumber17 == 16
replace districtname17 = "Mecklenburgische Seenplatte II – Landkreis Rostock III" if districtnumber17 == 17
replace districtname17 = "Hamburg-Mitte" if districtnumber17 == 18
replace districtname17 = "Hamburg-Altona" if districtnumber17 == 19
replace districtname17 = "Hamburg-Eimsbüttel" if districtnumber17 == 20
replace districtname17 = "Hamburg-Nord" if districtnumber17 == 21
replace districtname17 = "Hamburg-Wandsbek" if districtnumber17 == 22
replace districtname17 = "Hamburg-Bergedorf – Harburg" if districtnumber17 == 23
replace districtname17 = "Aurich – Emden" if districtnumber17 == 24
replace districtname17 = "Unterems" if districtnumber17 == 25
replace districtname17 = "Friesland – Wilhelmshaven – Wittmund" if districtnumber17 == 26
replace districtname17 = "Oldenburg – Ammerland" if districtnumber17 == 27
replace districtname17 = "Delmenhorst – Wesermarsch – Oldenburg-Land" if districtnumber17 == 28
replace districtname17 = "Cuxhaven – Stade II" if districtnumber17 == 29
replace districtname17 = "Stade I – Rotenburg II" if districtnumber17 == 30
replace districtname17 = "Mittelems" if districtnumber17 == 31
replace districtname17 = "Cloppenburg – Vechta" if districtnumber17 == 32
replace districtname17 = "Diepholz – Nienburg I" if districtnumber17 == 33
replace districtname17 = "Osterholz – Verden" if districtnumber17 == 34
replace districtname17 = "Rotenburg I – Heidekreis" if districtnumber17 == 35
replace districtname17 = "Harburg" if districtnumber17 == 36
replace districtname17 = "Lüchow-Dannenberg – Lüneburg" if districtnumber17 == 37
replace districtname17 = "Osnabrück-Land" if districtnumber17 == 38
replace districtname17 = "Stadt Osnabrück" if districtnumber17 == 39
replace districtname17 = "Nienburg II – Schaumburg" if districtnumber17 == 40
replace districtname17 = "Stadt Hannover I" if districtnumber17 == 41
replace districtname17 = "Stadt Hannover II" if districtnumber17 == 42
replace districtname17 = "Hannover-Land I" if districtnumber17 == 43
replace districtname17 = "Celle – Uelzen" if districtnumber17 == 44
replace districtname17 = "Gifhorn – Peine" if districtnumber17 == 45
replace districtname17 = "Hameln-Pyrmont – Holzminden" if districtnumber17 == 46
replace districtname17 = "Hannover-Land II" if districtnumber17 == 47
replace districtname17 = "Hildesheim" if districtnumber17 == 48
replace districtname17 = "Salzgitter – Wolfenbüttel" if districtnumber17 == 49
replace districtname17 = "Braunschweig" if districtnumber17 == 50
replace districtname17 = "Helmstedt – Wolfsburg" if districtnumber17 == 51
replace districtname17 = "Goslar – Northeim – Osterode" if districtnumber17 == 52
replace districtname17 = "Göttingen" if districtnumber17 == 53
replace districtname17 = "Bremen I" if districtnumber17 == 54
replace districtname17 = "Bremen II – Bremerhaven" if districtnumber17 == 55
replace districtname17 = "Prignitz – Ostprignitz-Ruppin – Havelland I" if districtnumber17 == 56
replace districtname17 = "Uckermark – Barnim I" if districtnumber17 == 57
replace districtname17 = "Oberhavel – Havelland II" if districtnumber17 == 58
replace districtname17 = "Märkisch-Oderland – Barnim II" if districtnumber17 == 59
replace districtname17 = "Brandenburg an der Havel – Potsdam-Mittelmark I – Havelland III – Teltow-Fläming I" if districtnumber17 == 60
replace districtname17 = "Potsdam – Potsdam-Mittelmark II – Teltow-Fläming II" if districtnumber17 == 61
replace districtname17 = "Dahme-Spreewald – Teltow-Fläming III – Oberspreewald-Lausitz I" if districtnumber17 == 62
replace districtname17 = "Frankfurt (Oder) – Oder-Spree" if districtnumber17 == 63
replace districtname17 = "Cottbus – Spree-Neiße" if districtnumber17 == 64
replace districtname17 = "Elbe-Elster – Oberspreewald-Lausitz II" if districtnumber17 == 65
replace districtname17 = "Altmark" if districtnumber17 == 66
replace districtname17 = "Börde – Jerichower Land" if districtnumber17 == 67
replace districtname17 = "Harz" if districtnumber17 == 68
replace districtname17 = "Magdeburg" if districtnumber17 == 69
replace districtname17 = "Dessau – Wittenberg" if districtnumber17 == 70
replace districtname17 = "Anhalt" if districtnumber17 == 71
replace districtname17 = "Halle" if districtnumber17 == 72
replace districtname17 = "Burgenland – Saalekreis" if districtnumber17 == 73
replace districtname17 = "Mansfeld" if districtnumber17 == 74
replace districtname17 = "Berlin-Mitte" if districtnumber17 == 75
replace districtname17 = "Berlin-Pankow" if districtnumber17 == 76
replace districtname17 = "Berlin-Reinickendorf" if districtnumber17 == 77
replace districtname17 = "Berlin-Spandau – Charlottenburg Nord" if districtnumber17 == 78
replace districtname17 = "Berlin-Steglitz-Zehlendorf" if districtnumber17 == 79
replace districtname17 = "Berlin-Charlottenburg-Wilmersdorf" if districtnumber17 == 80
replace districtname17 = "Berlin-Tempelhof-Schöneberg" if districtnumber17 == 81
replace districtname17 = "Berlin-Neukölln" if districtnumber17 == 82
replace districtname17 = "Berlin-Friedrichshain-Kreuzberg – Prenzlauer Berg Ost" if districtnumber17 == 83
replace districtname17 = "Berlin-Treptow-Köpenick" if districtnumber17 == 84
replace districtname17 = "Berlin-Marzahn-Hellersdorf" if districtnumber17 == 85
replace districtname17 = "Berlin-Lichtenberg" if districtnumber17 == 86
replace districtname17 = "Aachen I" if districtnumber17 == 87
replace districtname17 = "Aachen II" if districtnumber17 == 88
replace districtname17 = "Heinsberg" if districtnumber17 == 89
replace districtname17 = "Düren" if districtnumber17 == 90
replace districtname17 = "Rhein-Erft-Kreis I" if districtnumber17 == 91
replace districtname17 = "Euskirchen – Rhein-Erft-Kreis II" if districtnumber17 == 92
replace districtname17 = "Köln I" if districtnumber17 == 93
replace districtname17 = "Köln II" if districtnumber17 == 94
replace districtname17 = "Köln III" if districtnumber17 == 95
replace districtname17 = "Bonn" if districtnumber17 == 96
replace districtname17 = "Rhein-Sieg-Kreis I" if districtnumber17 == 97
replace districtname17 = "Rhein-Sieg-Kreis II" if districtnumber17 == 98
replace districtname17 = "Oberbergischer Kreis" if districtnumber17 == 99
replace districtname17 = "Rheinisch-Bergischer Kreis" if districtnumber17 == 100
replace districtname17 = "Leverkusen – Köln IV" if districtnumber17 == 101
replace districtname17 = "Wuppertal I" if districtnumber17 == 102
replace districtname17 = "Solingen – Remscheid – Wuppertal II" if districtnumber17 == 103
replace districtname17 = "Mettmann I" if districtnumber17 == 104
replace districtname17 = "Mettmann II" if districtnumber17 == 105
replace districtname17 = "Düsseldorf I" if districtnumber17 == 106
replace districtname17 = "Düsseldorf II" if districtnumber17 == 107
replace districtname17 = "Neuss I" if districtnumber17 == 108
replace districtname17 = "Mönchengladbach" if districtnumber17 == 109
replace districtname17 = "Krefeld I – Neuss II" if districtnumber17 == 110
replace districtname17 = "Viersen" if districtnumber17 == 111
replace districtname17 = "Kleve" if districtnumber17 == 112
replace districtname17 = "Wesel I" if districtnumber17 == 113
replace districtname17 = "Krefeld II – Wesel II" if districtnumber17 == 114
replace districtname17 = "Duisburg I" if districtnumber17 == 115
replace districtname17 = "Duisburg II" if districtnumber17 == 116
replace districtname17 = "Oberhausen – Wesel III" if districtnumber17 == 117
replace districtname17 = "Mülheim – Essen I" if districtnumber17 == 118
replace districtname17 = "Essen II" if districtnumber17 == 119
replace districtname17 = "Essen III" if districtnumber17 == 120
replace districtname17 = "Recklinghausen I" if districtnumber17 == 121
replace districtname17 = "Recklinghausen II" if districtnumber17 == 122
replace districtname17 = "Gelsenkirchen" if districtnumber17 == 123
replace districtname17 = "Steinfurt I – Borken I" if districtnumber17 == 124
replace districtname17 = "Bottrop – Recklinghausen III" if districtnumber17 == 125
replace districtname17 = "Borken II" if districtnumber17 == 126
replace districtname17 = "Coesfeld – Steinfurt II" if districtnumber17 == 127
replace districtname17 = "Steinfurt III" if districtnumber17 == 128
replace districtname17 = "Münster" if districtnumber17 == 129
replace districtname17 = "Warendorf" if districtnumber17 == 130
replace districtname17 = "Gütersloh I" if districtnumber17 == 131
replace districtname17 = "Bielefeld – Gütersloh II" if districtnumber17 == 132
replace districtname17 = "Herford – Minden-Lübbecke II" if districtnumber17 == 133
replace districtname17 = "Minden-Lübbecke I" if districtnumber17 == 134
replace districtname17 = "Lippe I" if districtnumber17 == 135
replace districtname17 = "Höxter – Lippe II" if districtnumber17 == 136
replace districtname17 = "Paderborn – Gütersloh III" if districtnumber17 == 137
replace districtname17 = "Hagen – Ennepe-Ruhr-Kreis I" if districtnumber17 == 138
replace districtname17 = "Ennepe-Ruhr-Kreis II" if districtnumber17 == 139
replace districtname17 = "Bochum I" if districtnumber17 == 140
replace districtname17 = "Herne – Bochum II" if districtnumber17 == 141
replace districtname17 = "Dortmund I" if districtnumber17 == 142
replace districtname17 = "Dortmund II" if districtnumber17 == 143
replace districtname17 = "Unna I" if districtnumber17 == 144
replace districtname17 = "Hamm – Unna II" if districtnumber17 == 145
replace districtname17 = "Soest" if districtnumber17 == 146
replace districtname17 = "Hochsauerlandkreis" if districtnumber17 == 147
replace districtname17 = "Siegen-Wittgenstein" if districtnumber17 == 148
replace districtname17 = "Olpe – Märkischer Kreis I" if districtnumber17 == 149
replace districtname17 = "Märkischer Kreis II" if districtnumber17 == 150
replace districtname17 = "Nordsachsen" if districtnumber17 == 151
replace districtname17 = "Leipzig I" if districtnumber17 == 152
replace districtname17 = "Leipzig II" if districtnumber17 == 153
replace districtname17 = "Leipzig-Land" if districtnumber17 == 154
replace districtname17 = "Meißen" if districtnumber17 == 155
replace districtname17 = "Bautzen I" if districtnumber17 == 156
replace districtname17 = "Görlitz" if districtnumber17 == 157
replace districtname17 = "Sächsische Schweiz-Osterzgebirge" if districtnumber17 == 158
replace districtname17 = "Dresden I" if districtnumber17 == 159
replace districtname17 = "Dresden II – Bautzen II" if districtnumber17 == 160
replace districtname17 = "Mittelsachsen" if districtnumber17 == 161
replace districtname17 = "Chemnitz" if districtnumber17 == 162
replace districtname17 = "Chemnitzer Umland – Erzgebirgskreis II" if districtnumber17 == 163
replace districtname17 = "Erzgebirgskreis I" if districtnumber17 == 164
replace districtname17 = "Zwickau" if districtnumber17 == 165
replace districtname17 = "Vogtlandkreis" if districtnumber17 == 166
replace districtname17 = "Waldeck" if districtnumber17 == 167
replace districtname17 = "Kassel" if districtnumber17 == 168
replace districtname17 = "Werra-Meißner – Hersfeld-Rotenburg" if districtnumber17 == 169
replace districtname17 = "Schwalm-Eder" if districtnumber17 == 170
replace districtname17 = "Marburg" if districtnumber17 == 171
replace districtname17 = "Lahn-Dill" if districtnumber17 == 172
replace districtname17 = "Gießen" if districtnumber17 == 173
replace districtname17 = "Fulda" if districtnumber17 == 174
replace districtname17 = "Main-Kinzig – Wetterau II – Schotten" if districtnumber17 == 175
replace districtname17 = "Hochtaunus" if districtnumber17 == 176
replace districtname17 = "Wetterau I" if districtnumber17 == 177
replace districtname17 = "Rheingau-Taunus – Limburg" if districtnumber17 == 178
replace districtname17 = "Wiesbaden" if districtnumber17 == 179
replace districtname17 = "Hanau" if districtnumber17 == 180
replace districtname17 = "Main-Taunus" if districtnumber17 == 181
replace districtname17 = "Frankfurt am Main I" if districtnumber17 == 182
replace districtname17 = "Frankfurt am Main II" if districtnumber17 == 183
replace districtname17 = "Groß-Gerau" if districtnumber17 == 184
replace districtname17 = "Offenbach" if districtnumber17 == 185
replace districtname17 = "Darmstadt" if districtnumber17 == 186
replace districtname17 = "Odenwald" if districtnumber17 == 187
replace districtname17 = "Bergstraße" if districtnumber17 == 188
replace districtname17 = "Eichsfeld – Nordhausen – Kyffhäuserkreis" if districtnumber17 == 189
replace districtname17 = "Eisenach – Wartburgkreis – Unstrut-Hainich-Kreis" if districtnumber17 == 190
replace districtname17 = "Jena – Sömmerda – Weimarer Land I" if districtnumber17 == 191
replace districtname17 = "Gotha – Ilm-Kreis" if districtnumber17 == 192
replace districtname17 = "Erfurt – Weimar – Weimarer Land II" if districtnumber17 == 193
replace districtname17 = "Gera – Greiz – Altenburger Land" if districtnumber17 == 194
replace districtname17 = "Saalfeld-Rudolstadt – Saale-Holzland-Kreis – Saale-Orla-Kreis" if districtnumber17 == 195
replace districtname17 = "Suhl – Schmalkalden-Meiningen – Hildburghausen – Sonneberg" if districtnumber17 == 196
replace districtname17 = "Neuwied" if districtnumber17 == 197
replace districtname17 = "Ahrweiler" if districtnumber17 == 198
replace districtname17 = "Koblenz" if districtnumber17 == 199
replace districtname17 = "Mosel/Rhein-Hunsrück" if districtnumber17 == 200
replace districtname17 = "Kreuznach" if districtnumber17 == 201
replace districtname17 = "Bitburg" if districtnumber17 == 202
replace districtname17 = "Trier" if districtnumber17 == 203
replace districtname17 = "Montabaur" if districtnumber17 == 204
replace districtname17 = "Mainz" if districtnumber17 == 205
replace districtname17 = "Worms" if districtnumber17 == 206
replace districtname17 = "Ludwigshafen/Frankenthal" if districtnumber17 == 207
replace districtname17 = "Neustadt – Speyer" if districtnumber17 == 208
replace districtname17 = "Kaiserslautern" if districtnumber17 == 209
replace districtname17 = "Pirmasens" if districtnumber17 == 210
replace districtname17 = "Südpfalz" if districtnumber17 == 211
replace districtname17 = "Altötting" if districtnumber17 == 212
replace districtname17 = "Erding – Ebersberg" if districtnumber17 == 213
replace districtname17 = "Freising" if districtnumber17 == 214
replace districtname17 = "Fürstenfeldbruck" if districtnumber17 == 215
replace districtname17 = "Ingolstadt" if districtnumber17 == 216
replace districtname17 = "München-Nord" if districtnumber17 == 217
replace districtname17 = "München-Ost" if districtnumber17 == 218
replace districtname17 = "München-Süd" if districtnumber17 == 219
replace districtname17 = "München-West/Mitte" if districtnumber17 == 220
replace districtname17 = "München-Land" if districtnumber17 == 221
replace districtname17 = "Rosenheim" if districtnumber17 == 222
replace districtname17 = "Bad Tölz-Wolfratshausen – Miesbach" if districtnumber17 == 223
replace districtname17 = "Starnberg – Landsberg am Lech" if districtnumber17 == 224
replace districtname17 = "Traunstein" if districtnumber17 == 225
replace districtname17 = "Weilheim" if districtnumber17 == 226
replace districtname17 = "Deggendorf" if districtnumber17 == 227
replace districtname17 = "Landshut" if districtnumber17 == 228
replace districtname17 = "Passau" if districtnumber17 == 229
replace districtname17 = "Rottal-Inn" if districtnumber17 == 230
replace districtname17 = "Straubing" if districtnumber17 == 231
replace districtname17 = "Amberg" if districtnumber17 == 232
replace districtname17 = "Regensburg" if districtnumber17 == 233
replace districtname17 = "Schwandorf" if districtnumber17 == 234
replace districtname17 = "Weiden" if districtnumber17 == 235
replace districtname17 = "Bamberg" if districtnumber17 == 236
replace districtname17 = "Bayreuth" if districtnumber17 == 237
replace districtname17 = "Coburg" if districtnumber17 == 238
replace districtname17 = "Hof" if districtnumber17 == 239
replace districtname17 = "Kulmbach" if districtnumber17 == 240
replace districtname17 = "Ansbach" if districtnumber17 == 241
replace districtname17 = "Erlangen" if districtnumber17 == 242
replace districtname17 = "Fürth" if districtnumber17 == 243
replace districtname17 = "Nürnberg-Nord" if districtnumber17 == 244
replace districtname17 = "Nürnberg-Süd" if districtnumber17 == 245
replace districtname17 = "Roth" if districtnumber17 == 246
replace districtname17 = "Aschaffenburg" if districtnumber17 == 247
replace districtname17 = "Bad Kissingen" if districtnumber17 == 248
replace districtname17 = "Main-Spessart" if districtnumber17 == 249
replace districtname17 = "Schweinfurt" if districtnumber17 == 250
replace districtname17 = "Würzburg" if districtnumber17 == 251
replace districtname17 = "Augsburg-Stadt" if districtnumber17 == 252
replace districtname17 = "Augsburg-Land" if districtnumber17 == 253
replace districtname17 = "Donau-Ries" if districtnumber17 == 254
replace districtname17 = "Neu-Ulm" if districtnumber17 == 255
replace districtname17 = "Oberallgäu" if districtnumber17 == 256
replace districtname17 = "Ostallgäu" if districtnumber17 == 257
replace districtname17 = "Stuttgart I" if districtnumber17 == 258
replace districtname17 = "Stuttgart II" if districtnumber17 == 259
replace districtname17 = "Böblingen" if districtnumber17 == 260
replace districtname17 = "Esslingen" if districtnumber17 == 261
replace districtname17 = "Nürtingen" if districtnumber17 == 262
replace districtname17 = "Göppingen" if districtnumber17 == 263
replace districtname17 = "Waiblingen" if districtnumber17 == 264
replace districtname17 = "Ludwigsburg" if districtnumber17 == 265
replace districtname17 = "Neckar-Zaber" if districtnumber17 == 266
replace districtname17 = "Heilbronn" if districtnumber17 == 267
replace districtname17 = "Schwäbisch Hall – Hohenlohe" if districtnumber17 == 268
replace districtname17 = "Backnang – Schwäbisch Gmünd" if districtnumber17 == 269
replace districtname17 = "Aalen – Heidenheim" if districtnumber17 == 270
replace districtname17 = "Karlsruhe-Stadt" if districtnumber17 == 271
replace districtname17 = "Karlsruhe-Land" if districtnumber17 == 272
replace districtname17 = "Rastatt" if districtnumber17 == 273
replace districtname17 = "Heidelberg" if districtnumber17 == 274
replace districtname17 = "Mannheim" if districtnumber17 == 275
replace districtname17 = "Odenwald – Tauber" if districtnumber17 == 276
replace districtname17 = "Rhein-Neckar" if districtnumber17 == 277
replace districtname17 = "Bruchsal – Schwetzingen" if districtnumber17 == 278
replace districtname17 = "Pforzheim" if districtnumber17 == 279
replace districtname17 = "Calw" if districtnumber17 == 280
replace districtname17 = "Freiburg" if districtnumber17 == 281
replace districtname17 = "Lörrach – Müllheim" if districtnumber17 == 282
replace districtname17 = "Emmendingen – Lahr" if districtnumber17 == 283
replace districtname17 = "Offenburg" if districtnumber17 == 284
replace districtname17 = "Rottweil – Tuttlingen" if districtnumber17 == 285
replace districtname17 = "Schwarzwald-Baar" if districtnumber17 == 286
replace districtname17 = "Konstanz" if districtnumber17 == 287
replace districtname17 = "Waldshut" if districtnumber17 == 288
replace districtname17 = "Reutlingen" if districtnumber17 == 289
replace districtname17 = "Tübingen" if districtnumber17 == 290
replace districtname17 = "Ulm" if districtnumber17 == 291
replace districtname17 = "Biberach" if districtnumber17 == 292
replace districtname17 = "Bodensee" if districtnumber17 == 293
replace districtname17 = "Ravensburg" if districtnumber17 == 294
replace districtname17 = "Zollernalb – Sigmaringen" if districtnumber17 == 295
replace districtname17 = "Saarbrücken" if districtnumber17 == 296
replace districtname17 = "Saarlouis" if districtnumber17 == 297
replace districtname17 = "St. Wendel" if districtnumber17 == 298
replace districtname17 = "Homburg" if districtnumber17 == 299

* Generate party variable

gen party17 = 1 if cdu17 == 1
replace party17 = 4 if spd17 == 1
replace party17 = 5 if fdp17 == 1
replace party17 = 7 if dielinke17 == 1
replace party17 = 6 if grune17 == 1
replace party17 = 322 if afd17 == 1
replace party17 = 180 if freiewahler17 == 1
replace party17 = 1 if csu17 == 1
replace party17 = 801 if party17 == . 

* Generate vote percentages

rename v17_1_ numberofvotes17

gen valid17 = strpos(var, "Gültige") > 0

gen validvotes17 = numberofvotes17 if valid17 == 1

gsort districtname17 validvotes17
bysort districtname17 : gen numberofvalidvotes17 = validvotes17[1]

drop if strpos(var, "Wahlberechtigte_1_End") > 0
drop if strpos(var, "Wähler_1_End") > 0
drop if strpos(var, "Ungültige_1_End") > 0
drop if strpos(var, "Gültige_1_End") > 0

gsort districtname17 -numberofvotes17

bysort districtname17 : gen rank17 = _n

gen numberofvotespercent17 = (numberofvotes17/numberofvalidvotes17)*100

drop if numberofvotespercent17 == .

* Generate rank of parties

gen first17 = party17 if rank17 == 1
gen second17 = party17 if rank17 == 2
gen third17 = party17 if rank17 == 3

bysort districtname17 : gen winner17 = first17[1]
bysort districtname17 : gen runnerup17 = second17[2]
bysort districtname17 : gen thirdplace17 = third17[3]

* Generate margins of victory

bysort districtname17 : gen margin17 = numberofvotespercent17[1] - numberofvotespercent17[2]

* Generate effective number of electoral parties

gen sqrvotepr17 = (numberofvotespercent17/100)^2

bysort districtname17 : egen sumsqrvotepr17 = sum(sqrvotepr17)

gen enep17 = 1/sumsqrvotepr17

* Labelling parties

label define party 1 "CDU/CSU" 4 "SPD" 5 "FDP" 6 "Green" 7 "Die Linke" 180 "FW" 322 "AfD" 88 "Other"
label values winner17 runnerup17 thirdplace17 party

* Selecting, ordering and sorting variables

keep districtnumber17 districtname17 party17 rank17 numberofvotes17 numberofvotespercent17 margin17 enep17 winner17 runnerup17 thirdplace17

order districtnumber17 districtname17 party17 rank17 numberofvotes17 numberofvotespercent17 margin17 enep17 winner17 runnerup17 thirdplace17

sort districtnumber17 rank17

drop if rank17 > 3

* Create election variable

gen election17 = "2017 German federal election"

* Saving file

save DE17_results, replace

* Drop duplicates

duplicates tag districtnumber17, gen(dup)
duplicates drop districtnumber17, force
drop dup

* Merge with DE13_17_results.dta

merge 1:1 _n using DE13_17_results.dta

* Generate variable for incumbent party reelection

gen reelected17 = 1 if winner17 == winner13
replace reelected17 = 0 if reelected17!=1

* Saving file

save DE1317_results, replace